﻿//----------------------------------------------------------------------------
// Copyright (c) Pavel Tishkin 2012-2013.
//
// You must not remove this notice, or any other, from this software.
//----------------------------------------------------------------------------

namespace Lib.Dxp.Orm

open System.Web
open System.Reflection
open System.Runtime.CompilerServices
open System.Runtime.InteropServices
open Lib.Dxp.Core
open Lib.Dxp.Core.Web


(*
    Document storage 
    --------------------------------------------------------------------------
        can exists in following ways
        1) <OneTable> as "ExtendInfo" AS TEXT field in base table: Depricated
        2) <ExtTable> as record in table "baseTable_Ext" ( "ObjectID", "FieldName", "ValueO" AS SqlVariant, "ValueT" AS Text )
                                where ("ObjectID", "FieldName") is unique index
*)

exception ModelDependenceCyclicTable of string * obj * obj
exception ModelDependenceAbsent      of string * obj * obj

[<System.AttributeUsage(System.AttributeTargets.Property)>]
type ZIndex(idx : int) =
    inherit System.Attribute()
    member x.IndexZ = idx


[<AutoOpenAttribute>]
module DataValueAP =    



    let GetPropertiesSorted (x : 'a) : PropertyInfo[] =
        x.GetType().GetProperties() |> Array.sortBy (fun p -> p.MetadataToken)
        //|> Array.sortBy (fun p -> match p.GetCustomAttributes(typeof<ZIndex>, true) with
        //                          | [|x|] -> 
        //                            Log.Debug("-- token", p.Name, typeof<'a>.Name)
        //                            (x :?> ZIndex).IndexZ
        //                          | a -> 
        //                            Log.Debug("no token", a, typeof<'a>.Name)
        //                            p.MetadataToken
        //                          )
    

    open System
    open System.Reflection


    let  (|GTA0_STRING|GTA0_INT|GTA0_DATETIME|GTA0_DOUBLE|GTA0_DECIMAL|GTA0_LIST|GTA0_NONE|) (ty' : System.Type) = 
        let arrty   = ty'.GetGenericArguments()
        if arrty.Length < 1 then GTA0_NONE else 
        let ty      = arrty.[0]
        match ty.GUID with
        | x when x = TypeGuid.String    -> GTA0_STRING
        | x when x = TypeGuid.Int       -> GTA0_INT
        | x when x = TypeGuid.Double    -> GTA0_DOUBLE
        | x when x = TypeGuid.DateTime  -> GTA0_DATETIME
        | x when x = TypeGuid.Decimal   -> GTA0_DECIMAL
        | x when x = TypeGuid.List      -> GTA0_LIST ty
        | _ -> GTA0_NONE





type DataValueStatus = 
    | NonInitialized    // Default 
    | Loaded 
    | Updated 
    | Created


type IDataField = 
    abstract FieldName      : string            with get, set
    abstract Table          : DocumentTable     with get, set
    abstract TableName      : string            with get, set
    abstract IsDynamic      : bool
    abstract Status         : DataValueStatus   with get, set
    abstract IsNullable     : bool
    abstract IsWriteble     : bool              // value must be updated in DB
    abstract MaxLength      : int               with get, set
    abstract AsString       : string            with get, set
    abstract LoadNull       : unit -> unit
    abstract SetNull        : unit -> unit


/// Generic data holder
and DataValue<'a when 'a : equality>(initval : 'a) = 

    let mutable value           = initval
    let mutable isnull          = true
    let mutable isnullable      = true
    let mutable isdynamic       = false
    let mutable table           = Unchecked.defaultof<DocumentTable>
    let mutable status          = DataValueStatus.NonInitialized
    let mutable tablename       = ""
    let mutable fieldname       = ""
    let mutable maxlength       = 0

    let restate newstate        = status <- match status with | _ -> newstate

    new ()                      = DataValue<'a>(Unchecked.defaultof<'a>)

    interface IDataField with
        member x.FieldName      with get () = fieldname and set v = fieldname <- v
        member x.Table          with get () = table     and set v = table     <- v
        member x.TableName      with get () = tablename and set v = tablename <- v
        member x.IsDynamic      = isdynamic
        member x.Status         with get () = status    and set v = status <- v 
        member x.IsNullable     = isnullable
        member x.IsWriteble     = match status with Updated -> true | _ -> false
        member x.MaxLength      with get () = maxlength and set v = maxlength <- v
        member x.AsString       with get () = Serializer.Json.json x.Value
                                 and set tx = try x.Value <- Serializer.Json.unjson<'a> tx
                                              with _ -> ()
        member x.LoadNull()     = isnull <- true; status <- Loaded
        member x.SetNull()      = isnull <- true; status <- Loaded
        


    member this.DataField   = this :> IDataField

    member this.Value        with get() = value     
                             and  set v = 
                                        match box v with
                                        | null 
                                        | :? System.DBNull    -> 
                                                if not isnull || status = DataValueStatus.NonInitialized then
                                                    value <- Unchecked.defaultof<'a>; isnull <- true
                                                    restate Updated
                                        | _                   -> 
                                                if v <> value || isnull || status = DataValueStatus.NonInitialized then
                                                    value <- v; isnull <- false
                                                    restate Updated
                                                    //Log.Debug("Set ", this.FieldName, v, this.Status)
    member this.Load         with set v = this.Value <- v; status <- Loaded
    member this.LoadNull()              = isnull <- true; status <- Loaded
    member this.SetNull()               = isnull <- true; status <- Updated

    member x.MaxLength      with get () = maxlength and set v = maxlength <- v

    /// Surrent status of data
    member this.Status       with get() = status    and set v = status <- v

    /// True if data belongs Document storage
    member this.IsDynamic    with get() = isdynamic and set v = isdynamic <- v

    /// Name of field in database table or document storage
    member this.FieldName    with get() = fieldname and set v = fieldname <- v

    /// Name of field in database table or document storage
    member this.TableName    with get() = tablename and set v = tablename <- v

    /// Name of field in database table or document storage
    member this.Table        with get() = table and set v = table <- v

    /// True if field in database can be null
    member this.IsNullable   with get() = isnullable and set v = isnullable <- v

    /// True if field must be written to database on save
    member this.IsWriteble   with get() = match status with Updated -> true | _ -> false

    /// Reperesent value as <_ option>
    member this.ToOption()              = if isnull then None else Some value

    member this.Set(a : obj) = 
        let this' =  this |> MetaFieldDef.FieldToDef
        (this' : MetaFieldDef).Set(a)

    /// String representation of current value
    override x.ToString()               = sprintf "%A" x.Value

    ///
    ///
    ///
    ///
    ///
    //static member ( ==. ) (fld : DataValue<_>, value : _)   = 
    //        Q.cond " = "   (box fld |> MetaFieldDef.FieldToDef , value)
    member fld.Enable  (value : 'a)     = Q.calc " | "         (fld |> MetaFieldDef.FieldToDef, value)
    member fld.Disable (value : 'a)     = Q.calc " & ~"        (fld |> MetaFieldDef.FieldToDef, value)
    
    member fld.IsNull                   = 
        //Q.cond " IS "        (fld |> MetaFieldDef.FieldToDef, "NULL")
        MetaFieldCond(" IS "  , fld |> MetaFieldDef.FieldToDef, "NULL" )
    member fld.IsNotNull                = 
        //Q.cond " IS NOT "    (fld |> MetaFieldDef.FieldToDef, "NULL")
        MetaFieldCond(" IS NOT "  , fld |> MetaFieldDef.FieldToDef, "NULL" )
        
    // Any Flag Test
    member fld.HasAny  (value : 'a)     = Q.cond " <> "  (Q.calc " & " (fld |> MetaFieldDef.FieldToDef, value), "0")
    member fld.HasAll  (value : 'a)     = Q.cond " = "   (Q.calc " & " (fld |> MetaFieldDef.FieldToDef, value), value)

    member fld.Enable  (value : MetaFieldDef)     = Q.calc " | "         (fld |> MetaFieldDef.FieldToDef, value)
    member fld.Disable (value : MetaFieldDef)     = Q.calc " & ~"        (fld |> MetaFieldDef.FieldToDef, value)

    member fld.HasAny  (value : MetaFieldDef)     = Q.cond " <> "  (Q.calc " & " (fld |> MetaFieldDef.FieldToDef, value), "0")
    member fld.HasAll  (value : MetaFieldDef)     = Q.cond " = "   (Q.calc " & " (fld |> MetaFieldDef.FieldToDef, value), value)

    //
    static member inline ( +. )  (fld : IDataField, value : MetaFieldDef)   = Q.calc " + " (fld |> MetaFieldDef.FieldToDef, value)
    static member inline ( -. )  (fld : IDataField, value : MetaFieldDef)   = Q.calc " - " (fld |> MetaFieldDef.FieldToDef, value)
    static member inline ( *. )  (fld : IDataField, value : MetaFieldDef)   = Q.calc " * " (fld |> MetaFieldDef.FieldToDef, value)
    static member inline ( /. )  (fld : IDataField, value : MetaFieldDef)   = Q.calc " / " (fld |> MetaFieldDef.FieldToDef, value)

    static member inline ( ==. ) (fld : IDataField, value : MetaFieldDef)   = Q.cond " = "    (fld |> MetaFieldDef.FieldToDef, value)
    static member inline ( <>. ) (fld : IDataField, value : MetaFieldDef)   = Q.cond " <> "   (fld |> MetaFieldDef.FieldToDef, value)
    static member inline ( <. )  (fld : IDataField, value : MetaFieldDef)   = Q.cond " < "    (fld |> MetaFieldDef.FieldToDef, value)
    static member inline ( >. )  (fld : IDataField, value : MetaFieldDef)   = Q.cond " > "    (fld |> MetaFieldDef.FieldToDef, value)
    static member inline ( <=. ) (fld : IDataField, value : MetaFieldDef)   = Q.cond " <= "   (fld |> MetaFieldDef.FieldToDef, value)
    static member inline ( >=. ) (fld : IDataField, value : MetaFieldDef)   = Q.cond " >= "   (fld |> MetaFieldDef.FieldToDef, value)
    static member inline ( %%. ) (fld : IDataField, value : MetaFieldDef)   = Q.cond " LIKE " (fld |> MetaFieldDef.FieldToDef, value)

    static member inline ( ==. ) (fld : IDataField, value :IDataField)   = Q.cond " = "    (fld |> MetaFieldDef.FieldToDef, value |> MetaFieldDef.FieldToDef)
    static member inline ( <>. ) (fld : IDataField, value :IDataField)   = Q.cond " <> "   (fld |> MetaFieldDef.FieldToDef, value |> MetaFieldDef.FieldToDef)
    static member inline ( <. )  (fld : IDataField, value :IDataField)   = Q.cond " < "    (fld |> MetaFieldDef.FieldToDef, value |> MetaFieldDef.FieldToDef)
    static member inline ( >. )  (fld : IDataField, value :IDataField)   = Q.cond " > "    (fld |> MetaFieldDef.FieldToDef, value |> MetaFieldDef.FieldToDef)
    static member inline ( <=. ) (fld : IDataField, value :IDataField)   = Q.cond " <= "   (fld |> MetaFieldDef.FieldToDef, value |> MetaFieldDef.FieldToDef)
    static member inline ( >=. ) (fld : IDataField, value :IDataField)   = Q.cond " >= "   (fld |> MetaFieldDef.FieldToDef, value |> MetaFieldDef.FieldToDef)
    static member inline ( %%. ) (fld : IDataField, value :IDataField)   = Q.cond " LIKE " (fld |> MetaFieldDef.FieldToDef, value |> MetaFieldDef.FieldToDef)
    //

    //
    static member inline ( +. )  (fld : IDataField, value : 'a)   = Q.calc " + " (fld |> MetaFieldDef.FieldToDef, value)
    static member inline ( -. )  (fld : IDataField, value : 'a)   = Q.calc " - " (fld |> MetaFieldDef.FieldToDef, value)
    static member inline ( *. )  (fld : IDataField, value : 'a)   = Q.calc " * " (fld |> MetaFieldDef.FieldToDef, value)
    static member inline ( /. )  (fld : IDataField, value : 'a)   = Q.calc " / " (fld |> MetaFieldDef.FieldToDef, value)

    static member inline ( ==. ) (fld : IDataField, value : 'a)   = Q.cond " = "    (fld |> MetaFieldDef.FieldToDef, value)
    static member inline ( <>. ) (fld : IDataField, value : 'a)   = Q.cond " <> "   (fld |> MetaFieldDef.FieldToDef, value)
    static member inline ( <. )  (fld : IDataField, value : 'a)   = Q.cond " < "    (fld |> MetaFieldDef.FieldToDef, value)
    static member inline ( >. )  (fld : IDataField, value : 'a)   = Q.cond " > "    (fld |> MetaFieldDef.FieldToDef, value)
    static member inline ( <=. ) (fld : IDataField, value : 'a)   = Q.cond " <= "   (fld |> MetaFieldDef.FieldToDef, value)
    static member inline ( >=. ) (fld : IDataField, value : 'a)   = Q.cond " >= "   (fld |> MetaFieldDef.FieldToDef, value)
    static member inline ( %%. ) (fld : IDataField, value : 'a)   = Q.cond " LIKE " (fld |> MetaFieldDef.FieldToDef, value)
    //
    member fld.BitOn  (value : 'a)                                = Q.calc  " | "   (fld |> MetaFieldDef.FieldToDef, value)
    member fld.BitOff (value : 'a)                                = Q.calc  " & ~"  (fld |> MetaFieldDef.FieldToDef, value)
    static member inline ( |. ) (fld : IDataField, value : 'a)    = Q.calc  " | "   (fld |> MetaFieldDef.FieldToDef, value)
    static member inline ( &~. ) (fld : IDataField, value : 'a)   = Q.calc  " & ~"  (fld |> MetaFieldDef.FieldToDef, value)




    member inline fld.In(lo : 'a, hi : 'a)  = (Q.cond " >= "   ( fld |> MetaFieldDef.FieldToDef, lo)) &&. (Q.cond " <= "   ( fld |> MetaFieldDef.FieldToDef, hi))
    member inline fld.In(lo : MetaFieldDef, hi : MetaFieldDef)  = (Q.cond " >= "   ( fld |> MetaFieldDef.FieldToDef, lo)) &&. (Q.cond " <= "   ( fld |> MetaFieldDef.FieldToDef, hi))

    member inline fld.In(xs : obj list)  = 
        let fld =  fld |> MetaFieldDef.FieldToDef
        let xs' = { fld
                    with MetaType = Calc { args  = xs |> List.map (box >> Model.XtoObj >> Q.AutoParamQ)
                                           toSql = Seq.map Q.safeSqlObject >> ", ".join >> sprintf "(%s)" }}
        MetaFieldCond(" IN ", fld, xs')
            
    member this.As          = fun newtitle -> { ( this |> MetaFieldDef.FieldToDef) with TitleName = ref newtitle }
    member this.Count       = let this = this |> MetaFieldDef.FieldToDef in { this with MetaType = Aggregate ("COUNT({0})", this) }
    member this.Sum         = let this = this |> MetaFieldDef.FieldToDef in { this with MetaType = Aggregate ("SUM({0})"  , this) }
    member this.Avg         = let this = this |> MetaFieldDef.FieldToDef in { this with MetaType = Aggregate ("AVG({0})"  , this) }
    member this.Max         = let this = this |> MetaFieldDef.FieldToDef in { this with MetaType = Aggregate ("MAX({0})"  , this) }
    member this.Min         = let this = this |> MetaFieldDef.FieldToDef in { this with MetaType = Aggregate ("MIN({0})"  , this) }
    member this.Distinct    = let this = this |> MetaFieldDef.FieldToDef in { this with MetaType = Distinct  ("DISTINCT {0}" , this) }




//module internal DocumentTableModule = 
//    let Guid_DataValue_Type      = typeof<DataValue<_>>.GUID
//    let isPropCanBeCollected (pi : PropertyInfo) = 
//            (pi.MemberType &&& MemberTypes.Property) = MemberTypes.Property
//            && pi.PropertyType.GUID = Guid_DataValue_Type
//            && not (pi.Name.StartsWith "_")



and  DocumentStaticValues   = System.Collections.Generic.Dictionary<string, (DataValueStatus * obj)>
and  DocumentDynamicValues  = System.Collections.Generic.Dictionary<string, obj>


and DslBuilderItem = Model -> Model
and DslBuilderPlan = DslBuilderItem seq

///
///
///

and [<AbstractClass>] 
    //DocumentTable internal (session : Session, isExternalSession : bool) = 
    DocumentTable internal () = //session' : Session, isExternalSession : bool) = 
    let mutable _metaname               = ""
    let mutable _metafields             = [||] : string []
    let mutable _metadescriptors        = [||] : IDataField []
    let mutable _metatypes              = [||] : System.Type []
    let mutable _id                     = DataValue(0) 
    let mutable session : Session       = Unchecked.defaultof<_>
    let mutable isExternalSession       = false


    let GUID_DataValue             = typeof<DataValue<_>>.GUID
    let  (|TYPE_DATAVALUE|_|) (ty : System.Type) = 
        match ty.GUID with
        | x when x = GUID_DataValue    -> Some ty
        | _ -> None

    let  (|DATAFIELD|_|) (value : obj) (pi : PropertyInfo) = 
        match pi.PropertyType.GUID with
        | x when x = GUID_DataValue -> Some (value :?> IDataField)
        | _ -> None


    let  (|DATA_STRING|_|) (value : obj) (pi : PropertyInfo) = 
        match pi.PropertyType with
        | GTA0_STRING -> value :?> DataValue<string> |> Some
        | _ -> None

    let  (|DATA_INT|_|) (value : obj) (pi : PropertyInfo) = 
        match pi.PropertyType with
        | GTA0_INT   -> value :?> DataValue<int> |> Some
        | _ -> None

    let  (|DATA_DOUBLE|_|) (value : obj) (pi : PropertyInfo) = 
        match pi.PropertyType with
        | GTA0_DOUBLE   -> value :?> DataValue<double> |> Some
        | _ -> None

    let  (|DATA_DATETIME|_|) (value : obj) (pi : PropertyInfo)  = 
        match pi.PropertyType with
        | GTA0_DATETIME   -> value :?> DataValue<System.DateTime> |> Some
        | _ -> None

    let  (|DATA_DECIMAL|_|) (value : obj) (pi : PropertyInfo) = 
        match pi.PropertyType with
        | GTA0_DECIMAL   -> value :?> DataValue<decimal> |> Some
        | _ -> None

    let  (|DATA_LIST|_|) (pi : PropertyInfo)  = 
        match pi.PropertyType with
        | GTA0_LIST gta  -> Some gta
        | _ -> None

    let  (|DATA_LIST_INT|_|) (value : obj) (pi : PropertyInfo)  = 
        match pi.PropertyType with
        | GTA0_LIST GTA0_INT -> value :?> DataValue<int list> |> Some
        | _ -> None

    let  (|DATA_LIST_STRING|_|) (value : obj) (pi : PropertyInfo)  = 
        match pi.PropertyType with
        | GTA0_LIST GTA0_STRING -> value :?> DataValue<string list> |> Some
        | _ -> None

    let  (|DATA_LIST_DATETIME|_|) (value : obj) (pi : PropertyInfo)  = 
        match pi.PropertyType with
        | GTA0_LIST GTA0_DATETIME      -> value :?> DataValue<System.DateTime list> |> Some
        | _ -> None

    let  (|DATA_LIST_DOUBLE|_|) (value : obj) (pi : PropertyInfo)  = 
        match pi.PropertyType with
        | GTA0_LIST GTA0_DOUBLE      -> value :?> DataValue<double list> |> Some
        | _ -> None

    let  (|DATA_LIST_DECIMAL|_|) (value : obj) (pi : PropertyInfo)  = 
        match pi.PropertyType with
        | GTA0_LIST GTA0_DECIMAL      -> value :?> DataValue<decimal list> |> Some
        | _ -> None





    let isPropCanBeCollected (pi : PropertyInfo) = 
            (pi.MemberType &&& MemberTypes.Property) = MemberTypes.Property
            && pi.PropertyType.GUID = GUID_DataValue
            && not (pi.Name.StartsWith "_")


    let fatal_static_as_list s  = Log.Log.Fatal (sprintf  "Cannot use static field `%s` to store list data" s)


    //new (externalSession)   = DocumentTable(externalSession, true)
    //new ()                  = DocumentTable(Session(), false)

    member x.Session with set v = session <- v; isExternalSession <- true

    /// Assign new Session and open it if x.session is unassigned. Else return x.session
    member this.SessionBegin()  = 
        if session = Unchecked.defaultof<_> then session <- Session()
        if not isExternalSession then session.Open() 
        session

    /// Commit Session if x.session is unassigned. Else nothins
    member this.SessionEnd()    = 
        if isExternalSession then () else
        session.Commit()
        session.Close()


    /// Master Key for table
    member x.Id   with get() = _id

    /// TODO: get true if field isText or big
    member x.IsFieldLong fldname = false

    /// Must be invoked after constructor. Maps names to fields 
    ///
    abstract init : unit -> unit
    default this.init() =
        let typed = this.GetType()
        let props = typed.GetProperties() |> Array.filter isPropCanBeCollected
        _metaname       <- typed.Name

        let fieldnames = ref []
        let dscriptbag = ref []
        for pi in props do
            match pi.GetValue(this, null) with
            | :? IDataField as value -> 
                    fieldnames :=  pi.Name :: !fieldnames
                    value.FieldName <- pi.Name; value.TableName <- _metaname; value.Table <- this
                    dscriptbag := value :: !dscriptbag
            | _ -> ()

        _metafields     <- !fieldnames |> List.toArray
        _metadescriptors<- !dscriptbag |> List.toArray
        _metatypes      <- props |> Array.map (fun pi -> pi.PropertyType.GetGenericArguments().[0])
     


    member this.CollectedFields() = this.GetType().GetProperties() |> Array.filter isPropCanBeCollected

    /// Return dynamic values of object as Dict<'FieldName, 'Value>
    ///
    member this.get_dynamicValuesAsDict() = 
           this.get_dynamicValuesAsDict(onlyChanged = false)

    member this.get_dynamicValuesAsDict(onlyChanged : bool) = 
        let typed = this.GetType()
        let props = typed.GetProperties() |> Array.filter isPropCanBeCollected
        let dict = DocumentDynamicValues()
        let isAnyValuesChanged = ref false
        
        let mapto (pi : PropertyInfo) ( e : DataValue<_> ) =
            let foldState x = isAnyValuesChanged := !isAnyValuesChanged || (x = Updated)
            let canCollect (x : DataValue<_>)  = not onlyChanged || x.Status = Updated
            if e.IsDynamic then begin foldState e.Status; if canCollect e then dict.Add(pi.Name, e.Value) end
        let maptoX (pi : PropertyInfo) ( e : IDataField ) =
            let foldState x = isAnyValuesChanged := !isAnyValuesChanged || (x = Updated)
            let canCollect (x : IDataField)  = not onlyChanged || x.Status = Updated
            if e.IsDynamic then begin foldState e.Status; if canCollect e then dict.Add(pi.Name, e.AsString) end

        for pi in props do
                let value = pi.GetValue(this, null)
                match pi with 
                | DATA_STRING   value x -> x |> mapto pi
                | DATA_INT      value x -> x |> mapto pi
                | DATA_DECIMAL  value x -> x |> mapto pi
                | DATA_DOUBLE   value x -> x |> mapto pi
                | DATA_DATETIME value x -> x |> mapto pi
                | DATAFIELD     value x -> x |> maptoX pi
                | _ -> ()
        !isAnyValuesChanged, dict    

(*
    member this.get_dynamicValuesAsDict'(onlyChanged : bool) = 
        let typed = this.GetType()
        let props = typed.GetProperties() |> Array.filter isPropCanBeCollected
        let dict = DocumentDynamicValues()
        let isAnyValuesChanged = ref false
        
        let mapto (pi : PropertyInfo) ( e : DataValue<_> ) =
            let foldState x = isAnyValuesChanged := !isAnyValuesChanged || (x = Updated)
            let canCollect (x : DataValue<_>)  = not onlyChanged || x.Status = Updated
            if e.IsDynamic then begin foldState e.Status; if canCollect e then dict.Add(pi.Name, e.Value) end

        for pi in props do
                let value = pi.GetValue(this, null)
                match pi with
                | DATA_STRING   value x -> x |> mapto pi
                | DATA_INT      value x -> x |> mapto pi
                | DATA_DECIMAL  value x -> x |> mapto pi
                | DATA_DOUBLE   value x -> x |> mapto pi
                | DATA_DATETIME value x -> x |> mapto pi
                | DATA_LIST _ -> 
                        match pi with
                        | DATA_LIST_STRING   value x -> x |> mapto pi
                        | DATA_LIST_INT      value x -> x |> mapto pi
                        | DATA_LIST_DECIMAL  value x -> x |> mapto pi
                        | DATA_LIST_DOUBLE   value x -> x |> mapto pi
                        | DATA_LIST_DATETIME value x -> x |> mapto pi
                        | _ -> ()
                | _ -> ()
        !isAnyValuesChanged, dict    
*)


    /// Return static values of object as isAnyValuesChanged * Dict<'FieldName, Status * 'Value>
    ///     <_ list> values cannot be static 
    member this.get_staticValuesAsDict() = 
           this.get_staticValuesAsDict(onlyChanged = false)

    member this.get_staticValuesAsDict(onlyChanged : bool) = 
        let typed = this.GetType()
        let props = typed.GetProperties() |> Array.filter isPropCanBeCollected
        let dict = netdict()
        let isAnyValuesChanged = ref false
        let foldState x     = isAnyValuesChanged := !isAnyValuesChanged || (x = Updated)
        let canCollect (x : DataValue<_>)  = not onlyChanged || x.Status = Updated
        let mapto (pi : PropertyInfo) ( x : DataValue<_> ) =
            if not x.IsDynamic then 
                foldState x.Status
                if canCollect x then 
                    dict.Add(pi.Name, (x.Status, box x.Value)) 

        for pi in props do
                let value = pi.GetValue(this, null)
                match pi with
                | DATA_STRING   value x     -> x |> mapto pi
                | DATA_INT      value x     -> x |> mapto pi
                | DATA_DECIMAL  value x     -> x |> mapto pi
                | DATA_DOUBLE   value x     -> x |> mapto pi
                | DATA_DATETIME value x     -> x |> mapto pi
                //| DATA_LIST _               -> fatal_static_as_list pi.Name
                | _ -> ()
        !isAnyValuesChanged, dict    


    member this.PPY = 
        let _, dict = this.get_staticSetFieldsAsDict(false)
        ", ".join(dict |> map (function KeyValue(k,v) -> sprintf "[%A,%A]"))

    member this.get_staticSetFieldsAsDict(onlyChanged : bool) = 
        let typed = this.GetType()
        let props = typed.GetProperties() |> Array.filter isPropCanBeCollected
        let dict = netdict()
        let isAnyValuesChanged = ref false
        let foldState x     = isAnyValuesChanged := !isAnyValuesChanged || (x = Updated)
        let canCollect (x : DataValue<_>)  = not onlyChanged || x.Status = Updated

        let mapto (pi : PropertyInfo) ( x : DataValue<_> ) =
            if not x.IsDynamic then 
                foldState x.Status
                if canCollect x then 
                        let y = x |> MetaFieldDef.FieldToDef
                        dict.Add(pi.Name, y.Set x.Value) 

        for pi in props do
                let value = pi.GetValue(this, null)
                match pi with
                | DATA_STRING   value x     -> x |> mapto pi
                | DATA_INT      value x     -> x |> mapto pi
                | DATA_DECIMAL  value x     -> x |> mapto pi
                | DATA_DOUBLE   value x     -> x |> mapto pi
                | DATA_DATETIME value x     -> x |> mapto pi
                //| DATA_LIST _               -> fatal_static_as_list pi.Name
                | _ -> ()
        !isAnyValuesChanged, dict    
        
    
    /// <string -> value> converter
    member this.load_AnyFromDict(dict : System.Collections.Generic.IDictionary<string, obj>) =    
        let typed = this.GetType()
        let props = typed.GetProperties() |> Array.filter isPropCanBeCollected
        for pi in props do
            try
                match dict.TryGetValue pi.Name with
                | false, _      -> ()
                | _, newvalue   -> let value = pi.GetValue(this, null) 
                                   match pi with
                                   | DATA_STRING   value x     -> x.Load <- to_s newvalue
                                   | DATA_INT      value x     -> if to_s newvalue |> isNone then x.LoadNull() else x.Load <- to_s newvalue |> int
                                   | DATA_DECIMAL  value x     -> if to_s newvalue |> isNone then x.LoadNull() else x.Load <- to_s newvalue |> decimal
                                   | DATA_DOUBLE   value x     -> if to_s newvalue |> isNone then x.LoadNull() else x.Load <- to_s newvalue |> float
                                   | DATA_DATETIME value x     -> if to_s newvalue |> isNone then x.LoadNull() else x.Load <- to_s newvalue |> System.DateTime.Parse
                                   | DATAFIELD   value x -> x.AsString <- to_s newvalue; x.Status <- Loaded
                                   | _                   -> ()
            with
            | ex -> Log.Error (sprintf "Cannot load value %s on Id = %i" typed.Name this.Id.Value)


    /// Fill fields from request. Return failed field conversation
    member this.fromRequest (req : HttpRequest) =
            let dict' = dict( seq { for key in req.Form.AllKeys -> (key, req.Form.[key]) } )
            this.update_AnyFromDict dict' 
            


    /// <string -> value> converter
    member this.update_AnyFromDict(dict : System.Collections.Generic.IDictionary<string, string>) : (string * string) list =    
        let typed = this.GetType()
        let props = typed.GetProperties() |> Array.filter isPropCanBeCollected
        let fails = ref []
        let mapto (x, ty) = 
            ServiceStack.Text.JsonSerializer.DeserializeFromString(string x,  ty) |> unbox |> Seq.toList
        for pi in props do
            if pi.Name <> "Id" then
                try 
                    match dict.TryGetValue pi.Name with
                    | false, _ -> ()
                    | _, newval ->
                    let value               = pi.GetValue(this, null) 
                    let newval_s = to_s newval
                    match pi with
                    | DATA_STRING   value x     -> 
                            if not x.IsDynamic && x.MaxLength < newval_s.Length then
                                fails := (pi.Name, "~Msg-Error-MaxLength".localed.format(x.MaxLength)) :: !fails
                            x.Value <- newval_s
                    | DATA_INT      value x     -> if isNone newval_s then x.SetNull() else x.Value <- newval_s |> int
                    | DATA_DECIMAL  value x     -> if isNone newval_s then x.SetNull() else x.Value <- newval_s |> decimal
                    | DATA_DOUBLE   value x     -> if isNone newval_s then x.SetNull() else x.Value <- newval_s |> float
                    | DATA_DATETIME value x     -> if isNone newval_s then x.SetNull() else x.Value <- newval_s |> System.DateTime.Parse
                    | DATAFIELD     value x     -> if isNone newval_s then x.SetNull() else x.AsString <- newval_s 
                    | _ -> ()
                with
                | ex -> fails := (pi.Name, ex.Message) :: !fails
        !fails 
    


(*
    member this.load_AnyFromDict'(dict : System.Collections.Generic.IDictionary<string, obj>) =    
        let typed = this.GetType()
        let props = typed.GetProperties() |> Array.filter isPropCanBeCollected
        let mapto (x, ty) = 
            ServiceStack.Text.JsonSerializer.DeserializeFromString(string x,  ty) |> unbox |> Seq.toList
        for pi in props do
            try
                match dict.TryGetValue pi.Name with
                | false, _ -> ()
                | _, newvalue ->
                let value               = pi.GetValue(this, null) 
                //Log.Debug(pi.Name)
                match pi with
                | DATA_STRING   value x     -> x.Load <- to_s newvalue
                | DATA_INT      value x     -> if to_s newvalue |> isNone then x.LoadNull() else x.Load <- to_s newvalue |> int
                | DATA_DECIMAL  value x     -> if to_s newvalue |> isNone then x.LoadNull() else x.Load <- to_s newvalue |> decimal
                | DATA_DOUBLE   value x     -> if to_s newvalue |> isNone then x.LoadNull() else x.Load <- to_s newvalue |> float
                | DATA_DATETIME value x     -> if to_s newvalue |> isNone then x.LoadNull() else x.Load <- to_s newvalue |> System.DateTime.Parse
                | DATA_LIST _ -> 
                        match pi with
                        | DATA_LIST_STRING   value x    -> if to_s newvalue |> isNone then x.LoadNull() else x.Load <- mapto(newvalue,  typeof<string list>)         
                        | DATA_LIST_INT      value x    -> if to_s newvalue |> isNone then x.LoadNull() else x.Load <- mapto(newvalue,  typeof<int list>)            
                        | DATA_LIST_DECIMAL  value x    -> if to_s newvalue |> isNone then x.LoadNull() else x.Load <- mapto(newvalue,  typeof<decimal list>)        
                        | DATA_LIST_DOUBLE   value x    -> if to_s newvalue |> isNone then x.LoadNull() else x.Load <- mapto(newvalue,  typeof<float list>)          
                        | DATA_LIST_DATETIME value x    -> if to_s newvalue |> isNone then x.LoadNull() else x.Load <- mapto(newvalue,  typeof<System.DateTime list>)
                        | _ -> ()
                | _ -> ()
            with
            | ex -> Log.Error (sprintf "Cannot load value %s on Id = %i" typed.Name this.Id.Value)




    member this.update_AnyFromDict'(dict : System.Collections.Generic.IDictionary<string, string>) : (string * string) list =    
        let typed = this.GetType()
        let props = typed.GetProperties() |> Array.filter isPropCanBeCollected
        let fails = ref []
        let mapto (x, ty) = 
            ServiceStack.Text.JsonSerializer.DeserializeFromString(string x,  ty) |> unbox |> Seq.toList
        for pi in props do
            if pi.Name <> "Id" then
                try 
                    match dict.TryGetValue pi.Name with
                    | false, _ -> ()
                    | _, newval ->
                    let value               = pi.GetValue(this, null) 
                    let newval_s = to_s newval
                    //Log.Debug(sprintf "~ MAP %A" (pi.Name, value))
                    match pi with
                    | DATA_STRING   value x     -> 
                            
                            if not x.IsDynamic && x.MaxLength < newval_s.Length then
                                //Log.Debug ("overlength", pi.Name, x.MaxLength, newval_s.Length)
                                fails := (pi.Name, "~Msg-Error-MaxLength".localed.format(x.MaxLength)) :: !fails
                            x.Value <- newval_s
                    | DATA_INT      value x     -> if isNone newval_s then x.SetNull() else x.Value <- newval_s |> int
                    | DATA_DECIMAL  value x     -> if isNone newval_s then x.SetNull() else x.Value <- newval_s |> decimal
                    | DATA_DOUBLE   value x     -> if isNone newval_s then x.SetNull() else x.Value <- newval_s |> float
                    | DATA_DATETIME value x     -> if isNone newval_s then x.SetNull() else x.Value <- newval_s |> System.DateTime.Parse
                    | DATA_LIST _ -> 
                            match pi with
                            | DATA_LIST_STRING   value x    -> if isNone newval_s then x.SetNull() else x.Value <- mapto(newval,  typeof<string list>)         
                            | DATA_LIST_INT      value x    -> if isNone newval_s then x.SetNull() else x.Value <- mapto(newval,  typeof<int list>)            
                            | DATA_LIST_DECIMAL  value x    -> if isNone newval_s then x.SetNull() else x.Value <- mapto(newval,  typeof<decimal list>)        
                            | DATA_LIST_DOUBLE   value x    -> if isNone newval_s then x.SetNull() else x.Value <- mapto(newval,  typeof<float list>)          
                            | DATA_LIST_DATETIME value x    -> if isNone newval_s then x.SetNull() else x.Value <- mapto(newval,  typeof<System.DateTime list>)
                            | _ -> ()
                    | _ -> ()
                with
                | ex -> fails := (pi.Name, ex.Message) :: !fails
        !fails 
*)    
    
        

    member x._MetaTypes         = _metatypes
    member x._MetaFields        = _metafields
    member x._MetaDescriptors   = _metadescriptors
    member x._MetaName          = _metaname


    abstract save   : unit                  -> int    // current or generated Id
    abstract load   : int                   -> unit
    //abstract load   : MetaFieldCond         -> unit
    abstract delete : unit                  -> unit



and  MetaFieldType_Foo    = { bar : int }


and IValidator = { 
    GettingErrorText     : MetaFieldDef -> string -> string    // Fld -> value -> true if Ok
    JsRtChecker     : string
    ValidatorName   : string
    }


and  MetaFieldType_Join   = { joinAs : string ; primaryKey : MetaFieldDef ; foreingKey : MetaFieldDef ; joinwhere : MetaFieldCond list }
and  MetaFieldType_Calc   = { args : obj list ; toSql : obj list -> string }
and  MetaFieldType_Param  = { no : int ref ; value : obj ref }
and  MetaFieldType_Link   = { linkedTable : string ; linkedKey : string }

and MetaFieldType = 
    | UnknownField
    | DataField     of DocumentTable   // Table * FieldName
    | Join          of MetaFieldType_Join 
    | Calc          of MetaFieldType_Calc
    | Parameter     of MetaFieldType_Param
    | Link          of MetaFieldType_Link
    | Aggregate     of string * MetaFieldDef
    | Distinct      of string * MetaFieldDef
    //| SubModel      of DslBuilderPlan
    | SubModel      of Model


and MetaFieldDef = {
    MetaType                : MetaFieldType
    FieldName               : string
    TitleName               : string ref    // key to translator resource
    IsUnique                : bool
    IsReadOnly              : bool
    IsDynamicFormat         : string
    }

    with
        member x.IsKey with get() = x.FieldName.EndsWith "Id"

        static member Fake fldname = 
            { MetaFieldDef.Cons with TitleName = ref fldname; FieldName = fldname }

        static member FromTableField (doctable : DocumentTable) fldname = 
            { MetaFieldDef.Cons 
              with MetaType = DataField doctable; TitleName = ref fldname; FieldName = fldname }

        static member FieldToDef  (field : IDataField) = 
            let mapto pref (e : IDataField)  = 
                let dynamicfmt (e : IDataField) =
                    if e.IsDynamic then
                        "(SELECT Value{2} FROM {1}_Ext WHERE ObjectId = {1}.Id AND FieldName = '{0}')".format(
                            e.FieldName, e.TableName, pref)
                    else
                        ""
                { MetaFieldDef.FromTableField  e.Table e.FieldName with IsDynamicFormat = dynamicfmt e }
            match field.GetType() with
            | GTA0_INT      
            | GTA0_DECIMAL  
            | GTA0_DOUBLE   
            | GTA0_DATETIME -> mapto "O" field
            | _             -> mapto "T" field

        static member FieldToLink  (e : IDataField) = 
            let link_to = splitRe "ID" e.FieldName
            { MetaFieldDef.Cons with MetaType = Link {  linkedTable = link_to.[0]; linkedKey = "Id" }; 
                                 TitleName = ref e.FieldName; FieldName = e.FieldName; 
                                 }

        static member Cons =
            let that = {
                TitleName               = ref ""
                FieldName               = ""
                MetaType                = UnknownField
                IsUnique                = false
                IsReadOnly              = true
                IsDynamicFormat         = ""
                }
            that 

        member this.Entitle newtext = { this with TitleName = ref newtext }

        member this.TableName 
            with get() = match this.MetaType with 
                         | DataField doc    -> doc._MetaName 
                         | Join jn          -> jn.primaryKey.TableName
                         | _ -> ""

        member this.ToSqlText =
            match this.MetaType with
            | Distinct  (op, fld)
            | Aggregate (op, fld)       -> op.format(fld)
            | Parameter x               -> sprintf "@a%i" !x.no  
            | Join x                    -> "ON " + " AND ".join(x.joinwhere)
            | Calc e                    -> e.toSql e.args
            | DataField doc when isSome this.IsDynamicFormat -> this.IsDynamicFormat
            | DataField doc             -> Sql.Sformat.tableFieldName doc._MetaName this.FieldName
            | SubModel m                -> "(" +  m .query + ")"
            | _ when isSome this.FieldName -> this.FieldName
            | _                         -> "~ERROR"

        
        member this.HtmlId      
            with get()  = 
                match this.MetaType with
                | DataField doc             -> Sql.Sformat.htmlIdFor doc._MetaName this.FieldName
                | _                         -> "" //Sql.Sformat.htmlIdFor doc._MetaName fldName
        member this.HtmlPropId  
            with get()  = "__" + this.FieldName

        member this.AsTextView (value, safe)   = 
            to_s value |> (if safe then id else escape) 


        override x.ToString()   = x.ToSqlText 

        member this.IsAutoKey   = !this.TitleName = "ID" || !this.TitleName = "AID"
        member this.IsAggregate = match this.MetaType with Aggregate _ -> true | _ -> false
        member this.IsCalculate = match this.MetaType with Calc _  -> true | _ -> false
        member this.IsJoin      = match this.MetaType with Join _  -> true | _ -> false
        member this.IsLong      = match this.MetaType with DataField doc -> doc.IsFieldLong this.FieldName | _ -> false

        member this.IsHiddenCol = (!this.TitleName).StartsWith(" ")


        member this.Set(a : obj) : MetaFieldSet = MetaFieldSet(this, Q.AutoParamQ a)

        member this.Value 
            with set(a : obj) = 
                match this.MetaType with
                | Parameter x -> x.value := a
                | _ -> ()
            and get() =
                match this.MetaType with
                | Parameter x -> !x.value 
                | _ -> null
        



and MetaFieldCond (op: string, xs : obj list) = 
    static member ( &&. ) (a : obj, b : obj) = MetaFieldCond (" AND ", a, b )
    static member ( ||. ) (a : obj, b : obj) = MetaFieldCond (" OR " , a, b )

    member x.Args = xs
    new (op: string, a : obj, b : obj) = MetaFieldCond(op, [ a ; b ])
    override x.ToString() = "({0})".format(op.join xs)





and MetaFieldSet( baseModel : MetaFieldDef, value : obj ) = 
    member this.Value   with set(a : obj) = baseModel.Value <- a and get() = value
    member this.BaseField with get() = baseModel
                
    member x.Args : obj list    = [ baseModel; value ]
    override x.ToString()       = baseModel.ToSqlText



/// Static helper
and Q =

    static member safeSqlObject<'a>(value : 'a) : string =
        let inner value = 
            match box value with
            | :? string as x            -> "'{0}'".format(x |> no_quotes) 
            | :? MetaFieldDef as x      -> match x.MetaType with
                                           | Parameter x    -> sprintf "@a%i" !x.no //  |> box       // TODO:
                                           | _              -> x.ToString() 
            | _                         -> value |> no_quotes 
        inner value

    static member internal ParamT(value : 'a, title)      = 
        let repack value =  
            { MetaFieldDef.Cons with MetaType = Parameter { no = ref -1; value = ref value }; 
                                     TitleName = ref ( if isSome title then title else "") }
        match box value with
        | :? MetaFieldDef as mf -> match mf.MetaType with | Parameter _ -> mf | _ -> repack value
        | _                     -> repack value
    static member Param(value : 'a)              = Q.ParamT(value, null)

    static member AutoParamQ (o : obj) = 
        match o with
        | :? MetaFieldDef   -> o
        | _                 -> Q.Param o |> box

    static member calc (op : string) (fld : MetaFieldDef, value : obj) = 
        { fld with MetaType = Calc { args = [ Q.AutoParamQ fld ; Q.AutoParamQ value ]
                                     toSql = (fun xs -> "({0})".format(op.join(Seq.map Q.safeSqlObject xs))) }
                   TitleName = ref (!fld.TitleName + "_1") 
                                   } 
    /// (value |> to_s) AS title
    static member Calc(value : obj, title) =
        { MetaFieldDef.Cons with MetaType = Calc { toSql = (fun _ -> match box value with 
                                                                     | :? MetaFieldDef -> value |> to_s
                                                                     | _           -> value |> no_quotes) 
                                                 ; args = [] }
                             ; TitleName = ref title }


    static member cond (op : string) (fld : MetaFieldDef, value : obj) = 
        MetaFieldCond(op     , fld, Q.AutoParamQ value )

    static member Not (fld : MetaFieldCond) = 
        MetaFieldCond(""     , "NOT ", fld)



    static member NowDate()                 = sprintf "'%s'" <| System.DateTime.Now.ToString("yyyy-MM-dd")
    static member NowTime()                 = sprintf "'%s'" <| System.DateTime.Now.ToString("HH:mm:ss")
    static member SqlGetDateTime()          = Q.Calc ("GetDate()", "")




/// Extend 
and MetaFieldDef with
    //
    member fld.Enable  (value : 'a)     = Q.calc " | "         (fld, value)
    member fld.Disable (value : 'a)     = Q.calc " & ~"        (fld, value)
    
    member fld.IsNull                   = Q.cond " IS "        (fld, "NULL")
    member fld.IsNotNull                = Q.cond " IS NOT "    (fld, "NULL")
        
    // Any Flag Test
    member fld.HasAny  (value : 'a)     = Q.cond " <> "  (Q.calc " & " (fld, value), "0")
    member fld.HasAll  (value : 'a)     = Q.cond " = " (Q.calc " & " (fld, value), value)

    static member inline ( +. )  (fld : MetaFieldDef, value : 'a)   = Q.calc " + " (fld, value)
    static member inline ( -. )  (fld : MetaFieldDef, value : 'a)   = Q.calc " - " (fld, value)
    static member inline ( *. )  (fld : MetaFieldDef, value : 'a)   = Q.calc " * " (fld, value)
    static member inline ( /. )  (fld : MetaFieldDef, value : 'a)   = Q.calc " / " (fld, value)
                                   
    static member inline ( ==. ) (fld : MetaFieldDef, value : 'a)   = Q.cond " = "   (fld, value)
    //[<Extension>]
    //static member inline isEq (fld : MetaFieldDef, value : 'a)   = Q.cond " = "   (fld, value)

    static member inline ( <>. ) (fld : MetaFieldDef, value : 'a)   = Q.cond " <> "   (fld, value)
    static member inline ( <. )  (fld : MetaFieldDef, value : 'a)   = Q.cond " < "   (fld, value)
    static member inline ( >. )  (fld : MetaFieldDef, value : 'a)   = Q.cond " > "   (fld, value)
    static member inline ( <=. ) (fld : MetaFieldDef, value : 'a)   = Q.cond " <= "   (fld, value)
    static member inline ( >=. ) (fld : MetaFieldDef, value : 'a)   = Q.cond " >= "   (fld, value)
    static member inline ( %%. ) (fld : MetaFieldDef, value : 'a)   = Q.cond " LIKE " (fld, value)

    member inline fld.In(lo, hi)  = (Q.cond " >= "   (fld, lo)) &&. (Q.cond " <= "   (fld, hi))
    member inline fld.In(xs : obj list)  = 
        let xs' = { MetaFieldDef.Cons with MetaType = Calc { args  = xs |> List.map Q.AutoParamQ
                                                             toSql = Seq.map Q.safeSqlObject >> ", ".join >> sprintf "(%s)" }}
        MetaFieldCond(" IN ", fld, xs')
            
    member this.As          = fun newtitle -> { this with TitleName = ref newtitle }
    member this.Count       = { this with MetaType = Aggregate ("COUNT({0})", this) }
    member this.Sum         = { this with MetaType = Aggregate ("SUM({0})"  , this) }
    member this.Avg         = { this with MetaType = Aggregate ("AVG({0})"  , this) }
    member this.Max         = { this with MetaType = Aggregate ("MAX({0})"  , this) }
    member this.Min         = { this with MetaType = Aggregate ("MIN({0})"  , this) }
    member this.Distinct    = { this with MetaType = Distinct  ("DISTINCT {0}" , this) }






    static member collect(val' : obj) : MetaFieldDef seq =
        match val' with
        | :? MetaFieldCond as g -> g.Args |> Seq.collect MetaFieldDef.collect
        | :? MetaFieldSet as g  -> g.Args |> Seq.collect MetaFieldDef.collect
        | :? MetaFieldDef as g ->
            Seq.append 
                (seq [g]) 
                (match g.MetaType with
                    | Parameter { value = value }   -> MetaFieldDef.collect value
                    | SubModel m                    -> MetaFieldDef.collector m._Flatten_Defs  // m._Flatten_Defs |> map box
                    | Calc { args = args }          -> args |> Seq.collect MetaFieldDef.collect
                    | Join { joinAs = ja ; foreingKey = fk ; joinwhere = jw } -> 
                            let xs = jw |> List.collect (fun e -> e.Args)
                            (box fk :: xs) |> Seq.collect MetaFieldDef.collect
                    | _ -> 
                    Seq.empty)
        | _ -> 
            Seq.empty

    static member collector(args : obj seq) : MetaFieldDef seq = 
        args |> Seq.collect MetaFieldDef.collect





and ModelState = | Void | WhereAssigned




and Table() =
    //let mutable __meta_tablename = ""

    let mutable f_meta_fields       = [||] : MetaFieldDef[]
    let mutable f_meta_tablename    = ""

    //member x._meta_tablename  with get() = __meta_tablename 
    //                           and set value = __meta_tablename <- value
    member x._meta_tablename  with get() = f_meta_tablename and set value = f_meta_tablename <- value
    member x._meta_fields     with get() = f_meta_fields    and set value = f_meta_fields    <- value
    



and ModelLimit  = { Skip : int; Take : int }
and ModelOption = | NoOption = 0 | CountOnly = 1

/// <summary>
///     Model
/// </summary>
and Model = { 
    State     : ModelState
    Selectors : MetaFieldDef list
    Setters   : MetaFieldSet list
    WhereBy   : MetaFieldCond list
    GroupBy   : MetaFieldDef list
    OrderBy   : MetaFieldDef list
    Limiters  : ModelLimit
    Option    : ModelOption
    ParamCnt  : int ref
    }
    with 
        static member XtoLink (x : IDataField) = MetaFieldDef.FieldToLink x

        static member XtoMetaFieldDef (x : obj) = 
                match x with
                | :? IDataField as x                                    -> MetaFieldDef.FieldToDef x
                | z when z.GetType().GUID = typeof<DataValue<_>>.GUID   -> MetaFieldDef.FieldToDef (x :?> IDataField)
                | z when z.GetType().GUID = typeof<MetaFieldDef>.GUID   -> unbox x
                | _ ->  raise <| exn (sprintf "E003: Cannot convert type `%A` to IDataField" <| x.GetType())
        static member XtoObj (x : obj) = 
                match x with
                | :? IDataField as x                                    -> MetaFieldDef.FieldToDef x |> box
                | z when z.GetType().GUID = typeof<DataValue<_>>.GUID   -> MetaFieldDef.FieldToDef (x :?> IDataField) |> box
                | _ -> x

        static member Cons = { 
            State     = Void
            Selectors = []
            Setters   = []
            WhereBy   = []
            GroupBy   = []
            OrderBy   = []
            Limiters  = { Skip = 0; Take = 0 }
            Option    = ModelOption.NoOption
            ParamCnt  = ref 0
            }

        member this._Flatten_Defs = 
            Seq.collect id [
                this.Selectors |> map box
                this.Setters   |> map box
                this.WhereBy   |> map box
                this.GroupBy   |> map box
                this.OrderBy   |> map box
                ] |> toList



        member this.but_xs(excepted : MetaFieldDef list) =
            match this.Setters with
            | []    -> { this with Selectors = this.Selectors |> List.filter(fun e -> excepted |> Seq.fold (fun a g -> a && g.ToSqlText <> e.ToSqlText) true) }
            | _     -> this
        member this.but(excepted : obj[]) = 
            this.but_xs(excepted |> Array.toList |> List.map Model.XtoMetaFieldDef)


        member this.only(selectable : MetaFieldDef list) =
            match this.Setters with
            | []    -> { this with Selectors = this.Selectors @ selectable }
            | _     -> this
        member this.only(selectable : obj[]) = 
            this.only(selectable |> Array.toList |> List.map Model.XtoMetaFieldDef)

        member this.update(valuedxs : MetaFieldSet list) =
            match this.Selectors with
            | []    -> { this with Setters = this.Setters @ valuedxs }
            | _     -> this


        member this.where(cond : MetaFieldCond) = { this with WhereBy = this.WhereBy @ [cond] }
            




        //
        //
        //
        member private this.prepareParamValues() = 
            this.ParamCnt := 0
            let inner new_field_xs = 
                match new_field_xs with
                | [] -> ()
                | _  -> 
                    let pars = new_field_xs |> MetaFieldDef.collector
                    for item in pars do
                        match item.MetaType with
                        | Parameter { no = n; value = v } ->
                            this.ParamCnt := !this.ParamCnt + 1
                            n := !this.ParamCnt
                            item.TitleName := !item.TitleName |> noneTo ("param{0}".format( !n ))
                        | _ -> ()
            inner this._Flatten_Defs


        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        member private this.DML_Select() =
            this.prepareParamValues()
            
            let fld_aggr, fld_plain = this.Selectors |> List.partition (fun e -> e.IsAggregate)
            let viewedfields = this.Selectors |> Seq.filter(fun e -> not e.IsJoin)
            let fields_part  = 
                    if this.Limiters.Skip > 0 then
                        viewedfields
                        |> Seq.mapi(fun i e -> Sql.Sformat.asSynonym e.ToSqlText (sprintf "xxx%i" i))
                    else
                        viewedfields
                        |> Seq.map(fun e -> Sql.Sformat.asTitle e.ToSqlText !e.TitleName)
                    |> Sql.Text.CommaNl.join

            let group_part = 
                    match fld_aggr, fld_plain with
                    | [] , _ -> ""
                    | _ , [] -> ""
                    | _ , xs -> xs |> Sql.Text.Comma.join |> Sql.Sformat.GroupBy

            let joined = ref []     
            let joints = 
                    this._Flatten_Defs 
                    |> MetaFieldDef.collector
                    |> Seq.filter (fun e -> e.IsJoin)
                    |> Seq.map (fun e -> match e.MetaType with 
                                         | Join jn  -> joined := jn.foreingKey.TableName :: jn.primaryKey.TableName :: !joined 
                                         | _ -> ()
                                         e)
                    |> Seq.toArray
            let jointsX = 
                    joints
                    |> Seq.collect (fun e -> match e.MetaType with 
                                             | Join jn  -> jn.foreingKey.TableName :: jn.primaryKey.TableName :: []
                                             | _ -> []
                                             )
                    |> Seq.toArray



            // ------------------------ FROM
            let part_from = 

                let table_from_fields = 
                        this._Flatten_Defs 
                        |> MetaFieldDef.collector
                        |> Seq.choose (function { MetaType = Calc _ } -> None | x -> Some x.TableName)
                        |> Seq.distinct 
                        |> Seq.toArray 

                let tables_2_joins = !joined 
                let tables_2_plain = Set.op_Subtraction (set table_from_fields, set tables_2_joins) |> Set.toArray 

                let mutable part_from_join = ""
                if (joints |> isSome) && (joints.Length > 0) then
                    part_from_join <- joints.[0].TableName
                    for item in joints do
                        match item with
                        | { MetaType = Join x } -> 
                                part_from_join <- part_from_join + Sql.Sformat.joinOn x.joinAs x.foreingKey.TableName (item |> to_s)
                        | _ -> ()
            
                let part_from_plain = Sql.Text.Comma.join(tables_2_plain |> Seq.filter isSome)

                let is_join_n_simple = part_from_join |> isSome && part_from_plain |> isSome
                let clue = if is_join_n_simple then Sql.Text.Comma else ""
                part_from_plain + clue + part_from_join
                                



            let part_orderBy_fields = 
                //if this.Option &&& ModelOption.CountOnly = ModelOption.CountOnly then "" else 
                    this.OrderBy 
                    |> Seq.map(fun g -> g.ToSqlText + if (!g.TitleName).StartsWith "-" then Sql.Text.Part.Desc else "")
                    |> Sql.Text.Comma.join

            let part_select_fields = 
                let fields_or_count = 
                        if this.Option &&& ModelOption.CountOnly = ModelOption.CountOnly 
                            then "COUNT (*) " 
                            else fields_part
                match this.Limiters.Take, this.Limiters.Skip with 
                | 0 , 0 -> fields_or_count
                | i , 0 -> sprintf "TOP %i " i + fields_or_count
                | i , n -> fields_or_count 
                                            + ", ROW_NUMBER() OVER (ORDER BY " 
                                            + if isSome part_orderBy_fields then part_orderBy_fields else "ID"
                                            + ") AS row_enumerator "
                
            let mutable final = Sql.Sformat.SelectFrom part_select_fields part_from
            final <- this.add_where_conditions(final, false) + group_part

            final <- 
                if this.Option &&& ModelOption.CountOnly = ModelOption.CountOnly 
                    then final
                    else if this.Limiters.Skip = 0 then
                            match this.OrderBy with
                                 | [] -> final
                                 | _  -> Sql.Sformat.ExtendWithOrderBy final part_orderBy_fields
                else 
                    "SELECT " 
                    + (viewedfields |> Seq.mapi(fun i e -> Sql.Sformat.asTitle (sprintf "xxx%i" i) !e.TitleName) |> Sql.Text.CommaNl.join)
                    + "FROM ("
                    + final
                    + if this.Limiters.Take > 0
                      then
                        sprintf ") AS MyDerivedTable WHERE MyDerivedTable.row_enumerator BETWEEN %i AND %i" 
                              (this.Limiters.Skip + 1) 
                              (this.Limiters.Skip + this.Limiters.Take)
                      else
                        sprintf ") AS MyDerivedTable WHERE MyDerivedTable.row_enumerator >= %i " 
                              (this.Limiters.Skip + 1)
            final


        ///
        ///
        ///
        member internal this.join_table(ft : Table, relate : string, st : Table, join_where : MetaFieldCond list) =
            let relater = relate + " JOIN"
            let getKeyTable (m : Table)                 = 
                let key = m._meta_fields |> Seq.tryFind (fun e -> e.IsKey)
                key, m._meta_fields.[0].TableName
            let scanXonT (m : Table) (relateOn :string) = 
                m._meta_fields |> Seq.tryPick (fun e -> match e.MetaType with Link joinf when joinf.linkedTable = relateOn -> Some e | _ -> None)
            let i1key, i1table = getKeyTable ft
            let i2key, i2table = getKeyTable st
            let i1link, i2link = scanXonT ft i2table, scanXonT st i1table
            match i1key, i1link, i2key, i2link with
            | Some _, Some _, Some _, Some _ -> raise (ModelDependenceCyclicTable (sprintf "Cycled auto-relations between `%s` ans `%s`" i1table i2table, ft, st))
            | _     , Some f, Some s, _      
            | Some f, _     , _     , Some s -> this.join(f, relate, s, join_where)
            | _                              -> raise (ModelDependenceAbsent (sprintf "No auto-relations between `%s` ans `%s`" i1table i2table, ft, st))


        ///
        ///
        ///
        member internal this.join(fstKey : obj, relate : string, sndKey : obj , join_where : MetaFieldCond list) =
            let fstKey = Model.XtoMetaFieldDef fstKey
            let sndKey = Model.XtoMetaFieldDef sndKey
            let relater = relate + " JOIN"
            let joint = { fstKey with MetaType = Join { joinAs = relater
                                                        primaryKey = fstKey
                                                        foreingKey = sndKey
                                                        joinwhere = [fstKey ==. sndKey] @ join_where  
                                                        }
                        }
            let r = { this with Selectors = this.Selectors @ [joint] }
            r





        ///
        ///
        ///
        member private this.IsTableInJoint(joints : MetaFieldDef[], e : string) =
            joints 
            |> Seq.exists (function | { MetaType = Join { foreingKey = fk } } as x -> x.TableName = e || fk.TableName = e 
                                    | _ -> false)

        member private this.add_where_conditions(_unrestricted_query : string , _autokey) = // = true)
            let y = this.WhereBy |> Sql.Text.NlAnd.join
            _unrestricted_query +
                    if y |> isSome then Sql.Sformat.Where y
                                   else (if _autokey then Sql.Sformat.Where "ID = @paramID" else "")


        member this.query   = this.DML_Select()



        static member internal consParam(value : obj, i) = 
            let reval = match value with
                        | null  -> box System.DBNull.Value
                        | _     -> value
            new System.Data.SqlClient.SqlParameter(Sql.Sformat.paramNo( i + 1 ), reval)

        member internal this.consCmd (query) = //, RowModel initRow)
            let cmd = new System.Data.SqlClient.SqlCommand(query, null);
            let args_from_fields = 
                    this._Flatten_Defs 
                    |> MetaFieldDef.collector
                    |> choose (function { MetaType = Parameter x } -> Some x | _ -> None)
                    |> mapi   (fun i { no = _; value = value } -> Model.consParam(!value, i))
                    |> toArray
            cmd.Parameters.AddRange args_from_fields
            cmd

        static member AsNonParamSQL(cmd : System.Data.SqlClient.SqlCommand) =
            let mutable x = cmd.CommandText;
            for i = cmd.Parameters.Count - 1 downto 0 do
                x <- x.Replace(Sql.Sformat.paramNo( i + 1 ),
                            "{0} /* x{1}/{2} */".format((cmd.Parameters.[i].Value |> Q.safeSqlObject), i + 1, cmd.Parameters.Count))
            x




        member internal this.TableName4Setters() =
            this.Setters 
            |> map (fun e -> e.BaseField.TableName)
            |> takeHead


        member private this.DML_Update() =
            this.prepareParamValues()
            let tablename = this.TableName4Setters()
            if (tablename |> isNone) then "NO-TABLENAME"
            else
                let settedFields = this.Setters 
                                   |> map (fun e -> "{0} = {1}".format(e.BaseField.FieldName, e.Value |> to_s))
                                   |> filter isSome 
                                   |> ", ".join
                this.add_where_conditions(Sql.Sformat.Update tablename settedFields, true)


        member private this.DML_Insert() =
            this.prepareParamValues()
            let tablename   = this.TableName4Setters()
            let setters     = this.Setters
            let fld_names   = setters |> map (fun e -> e.BaseField.FieldName) |> Sql.Text.Comma.join
            let val_params  = setters |> map (fun e -> to_s e.Value)
                                      |> Sql.Text.Comma.join
            Sql.Sformat.Insert tablename fld_names "" val_params ""
    
        member private this.DML_Delete() =
            this.prepareParamValues()
            let tablename = this.TableName4Setters()
            this.add_where_conditions(Sql.Sformat.Delete tablename, true)



        member this.CommandSelect =
                let cmd = this.consCmd(this.DML_Select())
                //Log.Qry(Debug.SqlCommand.asLogInfo cmd)
                cmd

        member this.CommandInsert = 
                let cmd = this.consCmd(this.DML_Insert())
                //Log.Qry(Debug.SqlCommand.asLogInfo cmd)
                cmd

        member this.CommandUpdate =
                let cmd = this.consCmd(this.DML_Update())
                //Log.Qry(Debug.SqlCommand.asLogInfo cmd)
                cmd

        member this.CommandDelete =
                let cmd = this.consCmd(this.DML_Delete())
                //Log.Qry(Debug.SqlCommand.asLogInfo cmd)
                cmd



module Dsl = 


    type On             = Left | Right | Inner | Outer

    let inner        = On.Inner
    let left         = On.Left
    let right        = On.Right
    let full         = On.Outer

    let private join2mode on =
        match on with
        | Left  -> "LEFT"
        | Right -> "RIGHT" 
        | Inner -> "INNER" 
        | Outer -> "FULL OUTER"

    let toFieldDefs (xs : obj list)         = xs |> List.map Model.XtoMetaFieldDef 
    let submodel x                          = { MetaFieldDef.Cons with MetaType = SubModel x }

    let select xs                           = fun (m : Model) -> m.only(Seq.toArray xs)
    let update xs                           = fun (m : Model) -> m.update(Seq.toList xs)
    let from (table : Table)                = fun (m : Model) -> m.only(table._meta_fields |> Array.toList)
    let except (xs : obj list)              = fun (m : Model) -> m.but(Seq.toArray xs)
    let where x                             = fun (m : Model) -> m.where(x)
    let join       fl (on : On) fr condxs   = fun (m : Model) -> m.join(fl, join2mode on, fr, condxs)
    // TODO: let join_table tl (on : On) tr condxs   = fun (m : Model) -> m.join_table(tl, join2mode on, tr, condxs)
    
    let order_by (xs : obj list)            = fun (m : Model) -> { m with OrderBy = m.OrderBy @ ( xs |> toFieldDefs ) }
    let order_by_desc (xs : obj list)       = fun (m : Model) -> { m with OrderBy = m.OrderBy @ ( xs |> toFieldDefs |> List.map (fun e -> e.Entitle ("-" + !e.TitleName))) }
    let skip n                              = fun (m : Model) -> { m with Limiters = { Skip = n; Take = m.Limiters.Take } }
    let take n                              = fun (m : Model) -> { m with Limiters = { Skip = m.Limiters.Skip; Take = n } }

    let count (xs : DslBuilderPlan)         = 
        { Seq.fold (fun acc f -> f acc) Model.Cons xs with Option = ModelOption.CountOnly }
    let model (xs : DslBuilderPlan)         = Seq.fold (fun acc f -> f acc) Model.Cons xs
    let query (xs : DslBuilderPlan)         = (model xs).query

    let cmd_select (xs : DslBuilderPlan)    = (model xs).CommandSelect
    let cmd_update (xs : DslBuilderPlan)    = (model xs).CommandUpdate
    let cmd_insert (xs : DslBuilderPlan)    = (model xs).CommandInsert
    let cmd_delete (xs : DslBuilderPlan)    = (model xs).CommandDelete


    type DocumentTable with
        member x.all 
            with get() = 
                let xs = x._MetaFields |> Array.map (MetaFieldDef.FromTableField x)
                Table(_meta_fields = xs , _meta_tablename = x._MetaName )
            

    let GUID_DataValue             = typeof<DataValue<_>>.GUID
    let  (|TYPE_DATAVALUE|_|) (ty : System.Type) = 
        match ty.GUID with
        | x when x = GUID_DataValue    -> Some ty
        | _ -> None


    let  (|DATA_STRING|_|) (value : obj) (pi : PropertyInfo) = 
        match pi.PropertyType with
        | GTA0_STRING -> value :?> DataValue<string> |> Some
        | _ -> None

    let  (|DATA_INT|_|) (value : obj) (pi : PropertyInfo) = 
        match pi.PropertyType with
        | GTA0_INT   -> value :?> DataValue<int> |> Some
        | _ -> None

    let  (|DATA_DOUBLE|_|) (value : obj) (pi : PropertyInfo) = 
        match pi.PropertyType with
        | GTA0_DOUBLE   -> value :?> DataValue<double> |> Some
        | _ -> None

    let  (|DATA_DATETIME|_|) (value : obj) (pi : PropertyInfo)  = 
        match pi.PropertyType with
        | GTA0_DATETIME   -> value :?> DataValue<System.DateTime> |> Some
        | _ -> None

    let  (|DATA_DECIMAL|_|) (value : obj) (pi : PropertyInfo) = 
        match pi.PropertyType with
        | GTA0_DECIMAL   -> value :?> DataValue<decimal> |> Some
        | _ -> None

    let  (|DATA_LIST|_|) (pi : PropertyInfo)  = 
        match pi.PropertyType with
        | GTA0_LIST gta  -> Some gta
        | _ -> None

    let  (|DATA_LIST_INT|_|) (value : obj) (pi : PropertyInfo)  = 
        match pi.PropertyType with
        | GTA0_LIST GTA0_INT -> value :?> DataValue<int list> |> Some
        | _ -> None

    let  (|DATA_LIST_STRING|_|) (value : obj) (pi : PropertyInfo)  = 
        match pi.PropertyType with
        | GTA0_LIST GTA0_STRING -> value :?> DataValue<string list> |> Some
        | _ -> None

    let  (|DATA_LIST_DATETIME|_|) (value : obj) (pi : PropertyInfo)  = 
        match pi.PropertyType with
        | GTA0_LIST GTA0_DATETIME      -> value :?> DataValue<System.DateTime list> |> Some
        | _ -> None

    let  (|DATA_LIST_DOUBLE|_|) (value : obj) (pi : PropertyInfo)  = 
        match pi.PropertyType with
        | GTA0_LIST GTA0_DOUBLE      -> value :?> DataValue<double list> |> Some
        | _ -> None

    let  (|DATA_LIST_DECIMAL|_|) (value : obj) (pi : PropertyInfo)  = 
        match pi.PropertyType with
        | GTA0_LIST GTA0_DECIMAL      -> value :?> DataValue<decimal list> |> Some
        | _ -> None

